﻿using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using VerticaAPI.model;
using static VerticaAPI.model.MatSuppleShopModel;

namespace VerticaAPI
{
    /// <summary>
    /// 端头陈列量
    /// </summary>
    [Description("店铺补货限制")]
    public class MatSuppleShopHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        private string CompanyId = string.Empty;
        private string ExtrSource = string.Empty;

        public MatSuppleShopHandler()
        {

        }

        #region 查询

        #region 店铺补货限制列表
        [MustLogin]
        [Description("店铺补货限制")]
        [ReturnType(typeof(IList<MatSuppleShopModel.MatSuppleShop>))]
        public ApiResult GetMatSuppleShoplist(
            [ParamDescription("店铺名称或者编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {
            try
            {
                CompanyId = UserInfo.UserCompanyId;
                ExtrSource = UserInfo.UserExtrSource;
                var count = ReturnCount(keys, materialId);
                var result = PaperData<IGrouping<string, MatSuppleShopModel.MatSuppleShop>>.Create(count, Size, Page);
                string sql =
                    $@"  SELECT  a.MaterialID,a.AllShopFlag,a.ExtrSource,a.CompanyID,a.ShopID,shop.ShopName as ShopName,a.LoadTime,a.EtlTime FROM ndw.ndw_MatSuppleShop a
                         LEFT JOIN ndw.ndw_Shop shop on a.shopid = shop.shopid
                         where a.ExtrSource='{ExtrSource}' and a.CompanyID='{CompanyId}'
                               {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (a.shopId ilike '%{keys}%' or shop.shopname like '%{keys}%') ")}
                               {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and a.materialId ilike '%{materialId}%' ")}
                                      LIMIT {Size} offset {(Page - 1.0) * Size}	";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<MatSuppleShopModel.MatSuppleShop>.ConvertToModel(dt).OrderBy(c => c.MaterialID);
                var groupedList = list.GroupBy(p => p.CompanyID + p.ShopID + p.MaterialID);
                result.Items = groupedList.ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
        #endregion

        #region  处理门店ID

        public string ReturnShopList(string str)
        {
            if (string.IsNullOrEmpty(str))
                return "";
            string returnStr = "'";
            string[] arr = null;
            arr = str.Split(',');

            for (int i = 0; i < arr.Length; i++)
            {
                returnStr += arr[i] + "','";
            }
            returnStr = returnStr.TrimEnd('\'');
            returnStr = returnStr.TrimEnd(',');

            return returnStr;
        }

        #endregion

        #region 返回行数

        public int ReturnCount(
            [ParamDescription("店铺名称或者店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "")
        {
            try
            {
                var countSql = string.Empty;

                #region 查询时获取行数

                countSql = $@" SELECT count(1) FROM ndw.ndw_MatSuppleShop a
                               LEFT JOIN ndw.ndw_Shop shop on a.ShopID = shop.ShopID and a.ExtrSource=shop.ExtrSource and a.CompanyID=shop.CompanyID
                               where a.ExtrSource='{ExtrSource}' and a.CompanyID='{CompanyId}'
                               {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (a.shopId ilike '%{keys}%' or shop.shopname like '%{keys}%') ")}
                               {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and a.materialId ilike '%{materialId}%' ")}";

                #endregion 查询时获取行数
                var cmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回行数

        #region 获取明细
        /// <summary>
        /// 获取明细
        /// </summary>
        /// <param name="materialId"></param>
        /// <returns></returns>
        [MustLogin]
        [Description("获取明细")]
        [ReturnType(typeof(IList<MatSuppleShopModel.MatSuppleShopDetail>))]
        public ApiResult GetMatSuppleShopDetail([ParamDescription("货号")]string materialId)
        {
            CompanyId = UserInfo.UserCompanyId;
            ExtrSource = UserInfo.UserExtrSource;
            MatSuppleShopDetail detail = new MatSuppleShopDetail { MaterialID = materialId };
            try
            {
                string sql = $@"  SELECT  a.MaterialID,a.AllShopFlag,a.ShopID
                         FROM ndw.ndw_MatSuppleShop a
                         where 1=1 
                        {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and a.materialId = '{materialId}' ")}";

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<MatSuppleShopModel.MatSuppleShop>.ConvertToModel(dt);
                if (list.Count > 0)
                {
                    int AllShopFlag = list.Select(c => c.AllShopFlag).Distinct().SingleOrDefault();
                    string shopIDs = string.Join(",", list.Select(c => c.ShopID)?.Distinct().ToArray());
                    detail.AllShopFlag = AllShopFlag;
                    detail.ShopIds = shopIDs;
                }
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
            return Success(detail);
        }
        #endregion

        #endregion


        #region 删除

        [JsonType]
        [MustLogin]
        [Description("删除数据")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Remove([ParamDescription("删除实体List")] MatSuppleShopDeleteModelList param)
        {
            CompanyId = UserInfo.UserCompanyId;
            ExtrSource = UserInfo.UserExtrSource;
            if (param == null || param.List.Count < 1)
            {
                return Error($"删除失败!");
            }
            HashSet<string> DeleteSql = new HashSet<string>();
            foreach (var item in param.List)
            {
                string deleteSql = $@"Delete FROM ndw.ndw_MatSuppleShop Where
                                    MaterialID='{item.MaterialID}' and
	                                ExtrSource='{ExtrSource}' and CompanyID='{CompanyId}';";
                DeleteSql.Add(deleteSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in DeleteSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            return Error($"删除失败!");
        }

        #endregion 删除


        #region Update 修改

        [MustLogin]
        [JsonType]
        [Description("修改")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("要插入的数据")] MatSuppleShopDetail param)
        {
            #region 前端参数验证 
            CompanyId = UserInfo.UserCompanyId;
            ExtrSource = UserInfo.UserExtrSource;
            if (string.IsNullOrWhiteSpace(param.MaterialID))
            {
                return Error($"修改失败！货号不能为空");
            }
            param.MaterialID = param.MaterialID.Trim();

            if (string.IsNullOrWhiteSpace(param.ShopIds))
            {
                return Error($"修改失败！店铺编号不能为空");
            }
            #endregion

            List<MatSuppleShopCoty> matsuppleshops = new List<MatSuppleShopCoty>();
            //删除
            string del_sql = $@"DELETE FROM ndw.ndw_MatSuppleShop  WHERE  MaterialID='{param.MaterialID}' AND
	                                ExtrSource='{ExtrSource}' AND CompanyID='{CompanyId}';";
            if (param.AllShopFlag == 1)
            {
                matsuppleshops.Add(new MatSuppleShopCoty { MaterialID = param.MaterialID, AllShopFlag = 1, CompanyID = CompanyId, ExtrSource = ExtrSource, ShopID = "" });
            }
            else
            {
                string[] strShopIDS = param.ShopIds.Split(new char[] { ',' });
                for (int i = 0; i < strShopIDS.Length; i++)
                {
                    var shopID = strShopIDS[i];
                    matsuppleshops.Add(new MatSuppleShopCoty { MaterialID = param.MaterialID, AllShopFlag = 0, CompanyID = CompanyId, ExtrSource = ExtrSource, ShopID = shopID });
                }
            }

            List<CommandInfo> list = new List<CommandInfo>();
            list.Add(new CommandInfo(del_sql, null));
            var dt = DataTableHelper.LinqToDataTable(matsuppleshops);
            int n = Helper.ExecuteSqlTran(list, dt, "ndw.ndw_MatSuppleShop");
            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }


        public bool AlreadyExists(string ShopCode, string materialID)
        {
            bool result = false;
            try
            {
                string strSql = $@" SELECT COUNT(CompanyID) as COUNT FROM ndw.ndw_ShelfEndDisPlay 
                            WHERE ShopCode ='{ShopCode}' and MaterialID='{materialID}' and  ExtrSource='{ExtrSource}' and CompanyID='{CompanyId}' ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }


        #endregion


        #region 导入
        /// <summary>
        /// 导入-保存数据
        /// </summary>
        /// <param name="list">导入 </param>
        /// <returns>DataTable 出错记录</returns>
        [Description("导入数据")]
        public DataTable ChangeList(IList<MatSuppleShopImportModel> list)
        {
            CompanyId = UserInfo.UserCompanyId;
            ExtrSource = UserInfo.UserExtrSource;

            #region 验证通过保存到 newlist，不通过的保存到 error_list
            //要保存的数据
            List<MatSuppleShop> newlist = new List<MatSuppleShop>();
            //出错的数据
            List<MatSuppleShopImportModel> error_list = new List<MatSuppleShopImportModel>();
            if (list.Count > 0)
            {
                var MateriaList = list.Select(c => new { c.MaterialID, c.AllShopFlag }).Distinct().ToList();
                string IsExSql = string.Empty;
                var MaterialIDs = MateriaList.Select(c => new { c.MaterialID }).Distinct();
                foreach (var item_ids in MaterialIDs)
                {
                    var group_count = MateriaList.Count(c => c.MaterialID == item_ids.MaterialID);
                    if (group_count > 1)
                    {
                        var err_item = list.Where(c => c.MaterialID == item_ids.MaterialID);
                        error_list.AddRange(err_item);
                    }
                    else
                    {
                        var item = MateriaList.FirstOrDefault(c => c.MaterialID == item_ids.MaterialID);
                        if (item.AllShopFlag == "no" || item.AllShopFlag == "0")
                        {
                            var ShopIDs = list.Where(c => c.MaterialID == item.MaterialID).Select(c => c.ShopID).Distinct().ToList();
                            var ex_shopIds = ShopIDs.AsEnumerable();
                            string shopids = "'" + string.Join("','", ShopIDs).Trim('\'').Trim(',') + '\'';

                            #region 店铺编号验证
                            IsExSql = $@"SELECT ShopID FROM ndw.ndw_Shop where ShopID IN ({shopids}) and ShopState = '正常营业' and CompanyID = '{CompanyId}' and ExtrSource = '{ExtrSource}'";
                            var cmd = Helper.GetSqlStringCommand(IsExSql);
                            var dt = Helper.ExecuteDataTable(cmd);
                            var savaItems = ModelConvertHelper<NM_ShopQuery>.ConvertToModel(dt)?.Select(c => new { c.ShopID }).ToList(); ;
                            //将正常的门店信息放到新的集合保存,并且进入下一个循环
                            if (savaItems.Count > 0)
                            {
                                ex_shopIds = ex_shopIds.Except(savaItems.Select(c => c.ShopID));
                                foreach (var savaitem in savaItems)
                                {
                                    newlist.Add(new MatSuppleShop { CompanyID = CompanyId, ExtrSource = ExtrSource, ShopID = savaitem.ShopID, AllShopFlag = 0, MaterialID = item.MaterialID });
                                }
                            }
                            #endregion

                            #region 店铺代码验证
                            var code_IsExSql = $@"SELECT ShopID,ShopCode FROM ndw.ndw_Shop where ShopCode IN ({shopids}) and ShopState = '正常营业' and CompanyID = '{CompanyId}' and ExtrSource = '{ExtrSource}'";
                            var code_cmd = Helper.GetSqlStringCommand(code_IsExSql);
                            var code_dt = Helper.ExecuteDataTable(code_cmd);
                            var code_savaItems = ModelConvertHelper<NM_ShopQuery>.ConvertToModel(code_dt).Select(c => new { c.ShopID, c.ShopCode }).ToList();
                            //将正常的门店信息放到新的集合保存,并且进入下一个循环
                            if (code_savaItems.Count > 0)
                            {
                                ex_shopIds = ex_shopIds.Except(code_savaItems.Select(c => c.ShopCode));
                                foreach (var code_savaitem in code_savaItems)
                                {
                                    newlist.Add(new MatSuppleShop { CompanyID = CompanyId, ExtrSource = ExtrSource, ShopID = code_savaitem.ShopID, AllShopFlag = 0, MaterialID = item.MaterialID });
                                }
                            }
                            #endregion

                            #region 出错的数据
                            var exList = ex_shopIds.ToList();
                            if (exList.Count > 0)
                            {
                                foreach (var ex_shopId in ex_shopIds)
                                {
                                    var err_item = list.Where(c => c.ShopID == ex_shopId && c.MaterialID == item.MaterialID && c.AllShopFlag == item.AllShopFlag);
                                    error_list.AddRange(err_item);
                                }
                            }
                            #endregion
                        }
                        else if (item.AllShopFlag == "yes" || item.AllShopFlag == "1")
                        {
                            newlist.Add(new MatSuppleShop { CompanyID = CompanyId, ExtrSource = ExtrSource, AllShopFlag = 1, MaterialID = item.MaterialID });
                        }
                        else
                        {
                            #region 出错的数据
                            var err_item = list.Where(c => c.MaterialID == item.MaterialID && c.AllShopFlag == item.AllShopFlag);
                            error_list.AddRange(err_item);
                            #endregion
                        }
                    }
                }
            }
            #endregion

            #region Sql
            //记录删除语句
            var dellist = new List<CommandInfo>();
            //记录增加语句
            var inslist = new List<CommandInfo>();
            foreach (var item in newlist)
            {
                //先删除数据 //shopID='{item.ShopID}' AND
                string sql = $@"DELETE FROM ndw.ndw_MatSuppleShop  WHERE  MaterialID='{item.MaterialID}' AND
	                                ExtrSource='{ExtrSource}' AND CompanyID='{CompanyId}';";
                dellist.Add(new CommandInfo(sql, null));
                //再进行插入
                sql = $@" INSERT INTO ndw.ndw_MatSuppleShop (ExtrSource,CompanyID,MaterialID,AllShopFlag,ShopID,LoadTime,EtlTime)
	                VALUES( '{CompanyId}', '{ExtrSource}','{item.MaterialID}','{item.AllShopFlag}','{item.ShopID}','{DateTime.Now}','{DateTime.Now}')";
                inslist.Add(new CommandInfo(sql, null));
            }
            //先删除
            Helper.ExecuteSqlTran(dellist);
            //插入
            Helper.ExecuteSqlTran(inslist);
            #endregion

            DataTable error_dt = DataTableHelper.LinqToDataTable(error_list);
            return error_dt;
        }
        #endregion
    }
}
